package com.duoduo.db;

import java.io.Serializable;
import java.util.List;

import com.duoduo.bean.NewsDetail;
import com.duoduo.bean.NewsTitle;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper {

	private static final long serialVersionUID = -3262887588408440781L;
	private static final String DB_NAME = "sanguoshanews.db";
	private static final int DB_VERSION = 10;

	public static final String NEWS_TABLE_NAME = "newstitle";
	public static final String NEWS_COLUMN_ID = "_id";
	public static final String NEWS_COLUMN_URL = "url";
	public static final String NEWS_COLUMN_CONTENT = "content";

	public static final String NEWSDetail_TABLE_NAME = "newsdetail";
	public static final String NEWSDetail_COLUMN_ID = "_id";
	public static final String NEWSDetail_COLUMN_URL = "url";
	public static final String NEWSDetail_COLUMN_TITLE = "title";
	public static final String NEWSDetail_COLUMN_PUBDATE = "pubdate";
	public static final String NEWSDetail_COLUMN_CONTENT = "content";

	private SQLiteDatabase database;
	private SanGuoShaOpenHelper openHelper;

	public DBHelper(Context context) {
		openHelper = new SanGuoShaOpenHelper(context);
		database = openHelper.getWritableDatabase();

	}

	public Cursor getAllNews() {
		String sql = String.format("select * from %s", NEWS_TABLE_NAME);
		return database.rawQuery(sql, null);
	}

	public void saveAllNews(List<NewsTitle> list) {
		// 新闻没获取到
		if (list == null || list.size() == 0) {
			return;
		}
		database.beginTransaction();

		try {
			deleteAllNews();
			for (NewsTitle obj : list) {
				saveNewsTitle(obj.getUrl(), obj.getContent());
			}
			database.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			database.endTransaction();
		}

	}

	public void saveNewsTitle(String url, String content) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(NEWS_COLUMN_URL, url);
		contentValues.put(NEWS_COLUMN_CONTENT, content);
		database.insertOrThrow(NEWS_TABLE_NAME, null, contentValues);
	}

	public void deleteOldNewsDetail() {
		String sql = String.format(
				"delete from %s where %s not in ( select %s from %s)",
				NEWSDetail_TABLE_NAME, NEWSDetail_COLUMN_URL, NEWS_COLUMN_URL,
				NEWS_TABLE_NAME);
		database.beginTransaction();
		try {
			database.execSQL(sql);
			database.setTransactionSuccessful();
		} finally {
			database.endTransaction();
		}
	}

	public NewsDetail getNewsDetail(String url) {
		if (url == null) {
			Log.i("db", "url is null");
			return null;
		}
		NewsDetail newsDetail = null;
		String sql = String.format("select * from %s where %s = ?",
				NEWSDetail_TABLE_NAME, NEWSDetail_COLUMN_URL);
		String[] args = { url };
		Cursor cursor = database.rawQuery(sql, args);
		if (cursor.moveToFirst()) {
			String uri = cursor.getString(cursor
					.getColumnIndex(NEWSDetail_COLUMN_URL));
			String title = cursor.getString(cursor
					.getColumnIndex(NEWSDetail_COLUMN_TITLE));
			String pubdate = cursor.getString(cursor
					.getColumnIndex(NEWSDetail_COLUMN_PUBDATE));
			String content = cursor.getString(cursor
					.getColumnIndex(NEWSDetail_COLUMN_CONTENT));
			newsDetail = new NewsDetail(uri, title, pubdate, content);
		}
		return newsDetail;
	}

	public void saveNewsDetail(NewsDetail o) {
		saveNewsDetail(o.getTitle(), o.getUrl(), o.getPubdate(), o.getContent());
	}

	public void saveNewsDetail(String title, String url, String pubdate,
			String content) {
		Log.i("db", title);
		ContentValues contentValues = new ContentValues();
		contentValues.put(NEWSDetail_COLUMN_TITLE, title);
		contentValues.put(NEWSDetail_COLUMN_URL, url);
		contentValues.put(NEWSDetail_COLUMN_PUBDATE, pubdate);
		contentValues.put(NEWSDetail_COLUMN_CONTENT, content);
		database.insertOrThrow(NEWSDetail_TABLE_NAME, null, contentValues);

	}

	public void deleteAllNews() {
		String sql = String.format("delete from %s", NEWS_TABLE_NAME);
		database.execSQL(sql);
	}

	public void close() {
		openHelper.close();
	}

	private class SanGuoShaOpenHelper extends SQLiteOpenHelper {

		public SanGuoShaOpenHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);

		}

		public void onCreate(SQLiteDatabase database) {
			String createNewsSql = String
					.format("create table %s ( %s INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, %s TEXT, %s TEXT );",
							NEWS_TABLE_NAME, NEWS_COLUMN_ID, NEWS_COLUMN_URL,
							NEWS_COLUMN_CONTENT);
			database.execSQL(createNewsSql);
			Log.i("db", createNewsSql);
			String createNewsDetail = String
					.format("create table %s ( %s INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, %s TEXT, %s TEXT, %s TEXT, %s TEXT );",
							NEWSDetail_TABLE_NAME, NEWSDetail_COLUMN_ID,
							NEWSDetail_COLUMN_URL, NEWSDetail_COLUMN_TITLE,
							NEWSDetail_COLUMN_PUBDATE,
							NEWSDetail_COLUMN_CONTENT);
			database.execSQL(createNewsDetail);
			Log.i("db", createNewsDetail);

		}

		public void onUpgrade(SQLiteDatabase database, int oldVersion,
				int newVersion) {
			database.execSQL("DROP TABLE IF EXISTS news");
			String news_sql = String.format("DROP TABLE IF EXISTS %s;",
					NEWS_TABLE_NAME);
			database.execSQL(news_sql);
			Log.i("db", news_sql);
			String detail_sql = String.format("DROP TABLE IF EXISTS %s;",
					NEWSDetail_TABLE_NAME);
			database.execSQL(detail_sql);
			Log.i("db", detail_sql);
			onCreate(database);
		}

	}

}
